IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetExpenseInfo2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetExpenseInfo2]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

---------------------------
--Modifications:
--08-21-2007	Performance improvements: (nolock), removed indexes for inserts
--09-14-2007	Fix for Cartesian Join
----------------------------------------
CREATE procedure [dbo].[GetExpenseInfo2]
( 
	@SessionId varchar(100),
	@domainUserID int,
	@includeProgramProjects bit = null,
	@fundNumber varchar(8) = null,
	@folderNumber varchar(16) = null,
	@bicsNumber varchar(10) = null,
	@walkerNumber varchar(10) = null,
	@sponsorNumber varchar(32) = null,
	@sponsorName varchar(64) = null,
	@sponsorTypeID int = null,
	@agreementTypeID int = null,
	@activityCode varchar(4)= null,
	@locationID int = null,
	@budgetStartDate datetime = null,
	@budgetEndDate datetime = null,
	@projectStartDate datetime = null,
	@projectEndDate datetime = null,
	@piID int = null,
	@chiefCodeList varchar(1000) = null,
	@sponsorGroupTypeID int = null,
	@budgetEndingDays int = null,
	@projectEndingDays int = null,
	@fundCreatedDays int = null,
	@lowBalance decimal(17,2) = null,
	@highBalance decimal(17,2) = null,
	@agreementStatusTypeList varchar(50)= null,
	@fundStatusTypeID int	= null,
	@categoryID int = null,
	@accountNumber varchar(10) = null,
	@expenseDateFrom datetime = null,
	@expenseDateTo datetime = null,
	@incomingFundNumber varchar(8) = null,
	@monthEndingDate datetime = null
)
as
begin
	set nocount on

	declare @accountID int
	if isnull(@accountNumber,'') = ''
		set @accountID = 0
	else
		set @accountID = (select Id from FinAccount where Number = @accountNumber)

	set @includeProgramProjects = isnull(@includeProgramProjects, 0)
	set @fundNumber = isnull(@fundNumber, '')
	set @folderNumber = isnull(@folderNumber, '')
	set @bicsNumber = isnull(@bicsNumber, '')
	set @walkerNumber = isnull(@walkerNumber, '')
	set @sponsorNumber = isnull(@sponsorNumber, '')
	set @sponsorName = isnull(@sponsorName, '')
	set @sponsorTypeID = isnull(@sponsorTypeID, 0)
	set @agreementTypeID = isnull(@agreementTypeID, 0)
	set @activityCode = isnull(@activityCode, '')
	set @locationID = isnull(@locationID, 0)
	set @budgetStartDate = isnull(@budgetStartDate, '01/01/1900')
	set @budgetEndDate = isnull(@budgetEndDate, '01/01/1900')
	set @projectStartDate = isnull(@projectStartDate, '01/01/1900')
	set @projectEndDate = isnull(@projectEndDate, '01/01/1900')
	set @piID = isnull(@piID, 0)
	set @chiefCodeList = isnull(@chiefCodeList, '')
	set @sponsorGroupTypeID = isnull(@sponsorGroupTypeID, 0)
	set @budgetEndingDays = isnull(@budgetEndingDays, 0)
	set @projectEndingDays = isnull(@projectEndingDays, 0)
	set @fundCreatedDays = isnull(@fundCreatedDays, 0)
	set @lowBalance = isnull(@lowBalance, -922337203685477)
	set @highBalance = isnull(@highBalance, 922337203685477)
	set @agreementStatusTypeList = isnull(@agreementStatusTypeList, '')
	set @fundStatusTypeID = isnull(@fundStatusTypeID, 1)
	set @categoryID = isnull(@categoryID, 0)
	set @accountID = isnull(@accountID, 0)
	set @incomingFundNumber = isnull(@incomingFundNumber, '')
	set @monthEndingDate = isnull(@monthEndingDate, '')

	declare @directCosts int
	set @directCosts = 2

	declare @indirectCosts int
	set @indirectCosts = 3

	declare @revenue int
	set @revenue = 1

	declare @awardedBudgetTypeId int
	set @awardedBudgetTypeId = 3

	declare @viewFinancialInfoBitValue int
	set @viewFinancialInfoBitValue = 4

	declare @viewPendingAgreementsBitValue int 
	set @viewPendingAgreementsBitValue = 32

	declare @authorizedRightsBitMask int
	set @authorizedRightsBitMask = @viewFinancialInfoBitValue + @viewPendingAgreementsBitValue

	declare @t0 datetime
	set @t0 = current_timestamp

	-- Compute the fund ID based on whether or not the invocation is from an overview or detail context. In the former case,
	-- the @incomingFundNumber is not specified. In the later case, the specified fund number will only be accepted for
	-- further processing only if the specified user has access to that fund.

	declare @fundID int
	if (@incomingFundNumber = '')
		set @fundId = 0
	else
	begin
		select @fundID = [ID] from AgrFund where FundNumber = @incomingFundNumber
	end

	-- Generate a list of funds that are accessible to the specified user. When in the detail context, there would only
	-- be one fund to worry about. Since this list of accessible funds is used multiple times, the list is stored in
	-- a permanent table for the user.

	IF OBJECT_ID('tempdb..#TempFundCollection') is not null drop table #TempFundCollection
	create table #TempFundCollection (FundID int not null, AccessLevel int not null)

	If (@incomingFundNumber = '') begin
		exec GetFundCollection @SessionId, @domainUserID, @authorizedRightsBitMask
		--Create Unique Index FundId on #TempFundCollection (FundId)
	end
	else
		insert into #TempFundCollection (FundID,AccessLevel) values (@fundID,@authorizedRightsBitMask)

	declare @t1 datetime
	set @t1 = current_timestamp
	print 'Step_1: ' + cast(datediff(ss, @t0, @t1) as varchar)

	-- The expense date range should be from the first day of the 'from' month/year to the last day of the 'to' month/year.
	-- The 'to' date is clamped to the first day of the current month/year.
	-- There are three scenairos to handle:
	-- 1. Both dates are specified
	-- 2. Neither dates are specified
	-- 3. One of the dates is specified
	-- 3a. If the 'from' date is specified, the 'to' date is the mininum of the last gl close date and the 'from' + 12 months
	-- 3b. If the 'to' date is specified, the 'from' date is 'to' - 12 months

	Declare @glPostAsOfDate DateTime
	EXEC GetRefreshDates 'gl', @glPostAsOfDate OUTPUT

	if ((@expenseDateTo is null) or (@expenseDateTo > @glPostAsOfDate))
		set @expenseDateTo = @glPostAsOfDate

	if (@expenseDateFrom is null)
		set @expenseDateFrom = '01/01/1900'		--dateadd(day, 1, dateadd(month, -12, @expenseDateTo))
	else
		set @expenseDateFrom = dateadd(day, -datepart(day, @expenseDateFrom) + 1, @expenseDateFrom)

	-- Retrieve the list of month-end dates between the from and to date range. This is saved in a table variable
	-- for later reuse.
	create table #dateRange ([ID] int primary key identity(1, 1), MonthEndingDate datetime)

	insert into #dateRange
		select Dates from dbo.GetDateRanges(@expenseDateFrom, @expenseDateTo)

	declare @t2 datetime
	set @t2 = current_timestamp
	print 'Step_2: ' + cast(datediff(ss, @t1, @t2) as varchar)

	-- Retrive account data and stored it in a table variable for later use. We are interested in all account categories other than
	-- indirect costs, investment income, and other income.
	create table #lookUp
	(
		CategoryID int,
		CategoryName varchar(256),
		AccountID int,
		AccountNumber varchar(265),
		ExpenseAmount money,
		ExpenseMonth datetime,
		FundID int,
		SortOrder int
	)
	--create index ExpenseMonth on #lookUp(ExpenseMonth)
	--create index CategoryIDExpenseMonth on #lookUp(CategoryID, ExpenseMonth)
	--create index AccountIDExpenseMonth on #lookUp(AccountID, ExpenseMonth)

	insert into #lookUp
		select 
			fc.[ID] as CategoryID,
			fc.[Name] as CategoryName,
			fa.[ID] as AccountID,
			fa.Number + ' - ' + [Description] as AccountNumber,
			isnull(mfaa.amount, 0) as ExpenseAmount,
			mfaa.MonthEndingDate as ExpenseMonth,
			mfaa.FundID,
			fc.SortOrder
		from 
			MonthlyFundAccountActuals mfaa (nolock)
			inner join FinAccount fa (nolock) on mfaa.AccountID = fa.[ID]
			inner join FinCategory fc (nolock) on fc.[ID] = fa.FinCategoryID	
			inner join #TempFundCollection af (nolock) on mfaa.FundID = af.FundID
		where
			fc.[Name] not in ('INDIRECT COSTS', 'INVESTMENT INCOME', 'OTHER INCOME')
 			and mfaa.MonthEndingDate between @expenseDateFrom and @expenseDateTo
			and (fc.[ID] = @categoryID or @categoryID = 0) 
			and (mfaa.AccountID = @accountID or @accountID = 0)
			and ((mfaa.FundID = @fundID) or (@fundID = 0))
			
		order by
			fc.[ID], MonthEndingDate, AccountID

	if @expenseDateFrom = '1900-01-01 00:00:00'
	begin
		select
			dr.MonthEndingDate as ExpenseMonth,
			sum(IsNull(l.ExpenseAmount,0)) as TotalExpenses
		into #tmp1
		from
			#lookUp l
			right outer join #dateRange dr on l.ExpenseMonth = dr.MonthEndingDate 
		group by
			dr.MonthEndingDate
		order by
			dr.MonthEndingDate asc

		declare @firstMonthWithExpenses datetime
		set @firstMonthWithExpenses = (select min(ExpenseMonth) from #tmp1 where TotalExpenses <> 0)

		delete	#dateRange
		where	MonthEndingDate < @firstMonthWithExpenses

		delete	#lookUp
		where	ExpenseMonth < @firstMonthWithExpenses
		drop table #tmp1
	end

	declare @t4 datetime
	set @t4 = current_timestamp
	print 'Step_3: ' + cast(datediff(ss, @t2, @t4) as varchar)

	-- Retrieve category information for each month-end date within the date range specified. This information will be
	-- used to generate multiple tables in the resultset and as such is stored in a table variable.
	-- Note that we need an entry for each category for each expense month. This is achieved in two steps:
	-- Step 1: use a cross join between the table which contains the months and the table which contains the category
	-- information. This gives us the cartesian product as required. The only thing to be careful about is not to
	-- include any column in the select list which may produce more results in the product than is required. This forces
	-- us to set the expense amount to 0 in this step.
	-- Step 2: update the expense amount (set to 0 in step 1) for each unique combination of category and expense month.
	-- I am sure there are other ways of achieving the same, but all of them either involve explicit iteration or cursors,
	-- neither of which would be as fast as simple using the intelligence of T-SQL itself.

	create table #accountCategory
	(
		CategoryID int,
		CategoryName varchar(256),
		SortOrder int,
		ExpenseAmount money,
		ExpenseMonth datetime,
		AccountID int,
		AccountNumber varchar(265)
	)
	--create index CategoryIDExpenseMonth on #accountCategory(CategoryID, ExpenseMonth)
	--create index AccountIDExpenseMonth on #accountCategory(AccountID, ExpenseMonth)

	declare @t5 datetime
	set @t5 = current_timestamp
	print 'Step_5: ' + cast(datediff(ss, @t4, @t5) as varchar)

	-- Retrieve account information for each month-end date within the date range specified. This information will be
	-- used to generate multiple tables in the resultset and as such is stored in a table variable.
	-- Note that we need an entry for each account for each expense month. This is achieved in two steps:
	-- Step 1: use a cross join between the table which contains the months and the table which contains the account
	-- information. This gives us the cartesian product as required. The only thing to be careful about is not to
	-- include any column in the select list which may produce more results in the product than is required. This forces
	-- us to set the expense amount to 0 in this step.
	-- Step 2: update the expense amount (set to 0 in step 1) for each unique combination of account and expense month.
	-- I am sure there are other ways of achieving the same, but all of them either involve explicit iteration or cursors,
	-- neither of which would be as fast as simple using the intelligence of T-SQL itself.

	-- Step 1:
	insert into #accountCategory
		select distinct CategoryID, CategoryName, SortOrder, 0 as ExpenseAmount, dr.MonthEndingDate, AccountID, AccountNumber
			from #dateRange dr
				cross join #lookUp lu
				--here was Cartesian Join..the where clause fixes the problem JML 0914-2007:
				where dr.MonthEndingDate = lu.ExpenseMonth

	-- Step 2:
	update c
		set ExpenseAmount = (select sum(isnull(ExpenseAmount, 0)) from #lookUp
			where AccountID = c.AccountID and ExpenseMonth = c.ExpenseMonth)
		from #accountCategory c
			inner join #lookUp lu on lu.AccountID = c.AccountID and lu.ExpenseMonth = c.ExpenseMonth

	-- The fourth table in the resultset contains account information for each expense month.
	select
		AccountID,
		AccountNumber,
		CategoryID,
		CategoryName,
		SortOrder,
		convert(DateTime, ExpenseMonth, 101) as ExpenseMonth,
		IsNull(ExpenseAmount,0) as ExpenseAmount
	from
		#accountCategory
	order by
		SortOrder, AccountID, CategoryID, ExpenseMonth

	-- The fifth table in the resultset is the agreegated expense for each account.
	select
		AccountID,
		AccountNumber,
		CategoryID,
		CategoryName,
		SortOrder,
		sum(IsNull(ExpenseAmount,0)) as ExpenseAmount
	from
		#accountCategory
	group by
		AccountID, AccountNumber, CategoryID, CategoryName, SortOrder
	order by
		SortOrder, AccountID

	declare @t6 datetime
	set @t6= current_timestamp
	print 'Step_6: ' + cast(datediff(ss, @t5, @t6) as varchar)

	drop table #accountCategory
	drop table #lookUp
	drop table #dateRange
	drop table #TempFundCollection
end



GO
